<?php
/*
 * code to display gannt chart report asynchronously in gannt.php
*/
//check user has logged in
include("../inc_files/utils/checksession.php");
// Create connection
include("../inc_files/utils/dbconnection.php");

//query for the project details (PM, Customer, Dates etc.)
$projectID = $_GET["projectid"];
$query = ("SELECT projects.ProjectTitle, customers.Company,
		projects.StartDate, projects.EndDate, staff.FirstName, staff.LastName FROM
		projects INNER JOIN customers ON projects.CustomerID = customers.CustomerID
		INNER JOIN staff ON projects.ProjectManager = staff.UserID WHERE projects.ProjectID = ?;");

if($stmt = $mysqli -> prepare($query))	{
	//bind the customerid to the query
	$stmt -> bind_param("s", $projectID);

	//execute the query
	$projectResults = $stmt -> execute();

	//store the results
	$stmt->store_result();

	//bind the results to variables
	$stmt -> bind_result($projectTitle, $customer, $startDate, $endDate, $PMlastName, $PMfirstName);

	//actually fetch the results
	$stmt->fetch();

	//close the statement
	$stmt -> close();

}

//get details of project tasks, progress and timesheet entries for tasks
$query = ("SELECT DISTINCT tasks.TaskID,  tasks.Title, tasks.Budget, tasks.StartDate,
    tasks.EndDate, tasks.PercentComplete, tasks.Project,  TL.TotalHoursWorked
FROM  tasks  LEFT JOIN  timesheet_lines ON tasks.TaskID = timesheet_lines.Task LEFT JOIN
    (SELECT Task, SUM(SunHours + MonHours + TueHours + WedHours +  ThurHours +  FriHours + SatHours) AS
	 TotalHoursWorked FROM timesheet_lines GROUP BY Task) AS TL ON tasks.TaskID = TL.Task
WHERE tasks.Deleted != 1 AND tasks.Project = '{$projectID}';");

$tasksResults = $mysqli->query( $query );
//count the number of returned results
$resultCount = $tasksResults->num_rows;
//if there are some results returned create a table showing the results
if( $resultCount > 0){
	$rownumber = 0;

	echo "<table class='datagrid'>";

	//convert the start and end dates to DateTime objects
	$projectStartDate = new DateTime($startDate);
	$projectEndDate = new DateTime($endDate);
	$projectDurationInterval = $projectStartDate->diff($projectEndDate); //a date interval object for the project duration
	$projectDurationDays = $projectDurationInterval->format('%a');  //numeric number of days	
	

	
	echo "<th style=\"width:5%px;\"  class=\"ganntchart\">Budget</th>";

	echo "<th style=\"width:5%px;\"  class=\"ganntchart\">Progress</th>";

	echo "<th style=\"width:90%;\" class=\"ganntchart\">Gannt Chart</th>";

	
	while($row = mysqli_fetch_array($tasksResults))
	{
		//calculate date and progress variables
		$taskStartDate = new DateTime($row['StartDate']);
		$taskEndDate = new DateTime($row['EndDate']);
		$duration = $taskEndDate->diff($taskStartDate);
		$daysToTaskStartInterval = $projectStartDate->diff($taskStartDate); //date interval
		$daysToTaskStart = $daysToTaskStartInterval->format('%R%a');  //numeric numver of days
		$daysTaskDurationInterval= $taskStartDate->diff($taskEndDate);
		$daysTaskDuration = $daysTaskDurationInterval->format('%R%a');
		$taskWidth = round(($daysTaskDuration/$projectDurationDays)*1000);
		$percentComplete = $row['PercentComplete'];
		$now = new DateTime("now");
		$daysLateInterval = $taskEndDate->diff($now);
		$daysLate = $daysLateInterval->format('%R%a');
		$daysDue = 0-$daysLate;


		
		
		
		if($rownumber %2 == 0)
		{
			echo "<tr class=\"datagrid evenrow\">";
		}
		else
		{
			echo "<tr class=\"datagrid oddrow\">";
		}
		//display the status column for budget
		$underspend = (int)($row['Budget']) - (int)($row['TotalHoursWorked']);
		$overspend = (int)($row['TotalHoursWorked']) - (int)($row['Budget']);

		echo "<td style=\"width:5%;\"  class=\"ganntchart\">";
		if($row['TotalHoursWorked'] > $row['Budget']){
			echo"<img src=\"../images/warning_32.png\"   height=\"32\" width=\"32\"
					title=\"This task is {$overspend} hours over budget\">";
		} else {
			echo"<img src=\"../images/apply_32.png\"   height=\"32\" width=\"32\"
					title=\"This task is currently {$underspend} hours under budget\">";
		}
		echo "</td>";
		
		//display the status column for progress
		echo "<td style=\"width:5%;\"  class=\"ganntchart\">";
		if($daysLate > 0 && $percentComplete < 100){
			echo"<img src=\"../images/overdue_32.png\"   height=\"32\" width=\"32\"
			title=\"This task is {$daysLateInterval->format('%a')} days overdue\">";
		} else if($daysLate > -7 && $percentComplete < 100) {
			echo"<img src=\"../images/due_soon_32.png\"   height=\"32\" width=\"32\"
			title=\"This task is due in {$daysDue} days\">";
			} else if($daysLate < -6 && $percentComplete < 100) {
				echo"<img src=\"../images/apply_32.png\"   height=\"32\" width=\"32\"
				title=\"Task is due on ".$taskEndDate->format('l jS \of F Y')."\">";
			} else {
				echo"<img src=\"../images/apply_32.png\"   height=\"32\" width=\"32\"
				title=\"Task due on ".$taskEndDate->format('l jS \of F Y')." and is now complete\">";
			}
		
		echo "</td>";
		
		echo "<td style=\"width:90%;\" class=\"ganntchart\">";
				


		
		//create a table with 1 row and 4 cols for each line of the GANNT chart
		echo"<table class=\"ganntchart\" width=\"1000\"><tr>";
		echo"<td colspan=\"4\" class=\"ganntcharttitle\">".$row['Title']." - ".$taskStartDate->format('l jS \of F Y')." until ".
		$taskEndDate->format('l jS \of F Y')." - ".$percentComplete."% complete</td></tr>
				<tr>";

		
		//work out the percentage widhts for the table cell to mark the start, progress, and end points of the
		//table cell for the GANNT chart
		$cell1width = (round(($daysToTaskStart/$projectDurationDays)*1000)); //permille width of 1st cell
		$cell2width = (round($taskWidth*($percentComplete/100))); //permille width of 2nd cell
		$cell3width = (round($taskWidth*(1-$percentComplete/100))); //permille width of 3rd cell
		$cell4width = (1000-($cell1width+$cell2width+$cell3width)); //permille width of 4th cell

		echo "<td class=\"ganntspacer\" style=\"width:".$cell1width."px;\"></td>";
		echo "<td class=\"ganntbar progress\" style=\"width:".$cell2width."px\"></td>";
		echo "<td class=\"ganntbar remaining\" style=\"width:".$cell3width."px\"></td>";
		echo "<td class=\"ganntspacer\" style=\"width:".$cell4width."px\"></td>";
		
		
		echo"</table></tr>";
		
		
		//end of GANNT chart line

		echo"</td>";
		

		echo "</tr>";
		$rownumber++;
	}
	echo "</table>";
}
//if no results returned say so.
else
{

	echo"No records returned";
}
//tidy up database connection
$mysqli->close();

//function to convert dates from dates entered by the user
//to dates for MySQL
function formatdate($date){
	$datearray = explode('/' , $date);
	$date = $datearray[2].'-'.$datearray[1].'-'.$datearray[0];
	return $date;

}

?>